/*
 * ReservaDAOImpl.java
 *
 * Created on 20 de noviembre de 2007, 20:01
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.fiuba.aterrizar.servidor.dal.dao.impl;

import com.fiuba.aterrizar.servidor.conf.ConnectionFactory;
import com.fiuba.aterrizar.servidor.dal.dao.ReservaDAO;
import com.fiuba.aterrizar.servidor.domain.Reserva;
import com.fiuba.aterrizar.servidor.domain.Vuelo;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Calendar;

/**
 *
 * @author Demian
 */
public class ReservaDAOImpl implements ReservaDAO{
    
    public Reserva findByCodigo(String codigo) {
        Connection conn = null;
        ConnectionFactory factory = ConnectionFactory.getInstance();
        PreparedStatement stm = null;
        try {
            conn = factory.getConnection();            
            stm = conn.prepareStatement("Select codigo,fecha,fechaCompra,codigoUsuario,codigoVuelo, clase FROM RESERVA where codigo=?");            
            stm.setString(1,codigo);            
            
            ResultSet rs = stm.executeQuery();
            if (rs.next()){                                
                Reserva reserva = new Reserva();
                reserva.setCodigo(rs.getString(1));
                reserva.setFecha(rs.getDate(2));
                reserva.setFechaCompra(rs.getDate(3));
                reserva.setCodigoUsuario(rs.getString(4));
                reserva.setCodigoVuelo(rs.getString(5));
                reserva.setClase(rs.getString(6));
                
                return reserva;                
            }
            
            return null;
        } catch (SQLException ex) {
            throw new RuntimeException("Error al buscar la reserva",ex);
        } finally {
            factory.closeStatement(stm);
            factory.closeConnection(conn);
        }
    }

    public void save(Reserva reserva) {
        Connection conn = null;
        ConnectionFactory factory = ConnectionFactory.getInstance();
        PreparedStatement stm = null;
        try {
            conn = factory.getConnection();            
            stm = conn.prepareStatement("INSERT INTO RESERVA (codigo,fecha,fechaCompra,codigoUsuario,codigoVuelo, clase) values (?,?,?,?,?,?)");
            stm.setString(1,reserva.getCodigo());
            stm.setDate(2,new java.sql.Date(reserva.getFecha().getTime()));
            if (reserva.getFechaCompra() != null){
                stm.setDate(3,new java.sql.Date(reserva.getFechaCompra().getTime()));
            }
            else{
                stm.setNull(3, Types.DATE);
            }
            stm.setString(4,reserva.getCodigoUsuario());
            stm.setString(5, reserva.getCodigoVuelo());
            stm.setString(6, reserva.getClase());
            
            stm.execute();            
        } catch (SQLException ex) {
            throw new RuntimeException("Error al guardar la reserva",ex);
        } finally {
            factory.closeStatement(stm);
            factory.closeConnection(conn);
        }
    }

    public void eliminarReservasVencidas() {
        Connection conn = null;
        ConnectionFactory factory = ConnectionFactory.getInstance();
        PreparedStatement stm = null;
        PreparedStatement stmUpdate = null;
        PreparedStatement stmDelete = null;
        ResultSet rs = null;
        try {
            conn = factory.getConnection();            
            stm = conn.prepareStatement("SELECT clase, codigoVuelo from RESERVA WHERE DATEDIFF(CURDATE(), fecha)  > 30");
            rs = stm.executeQuery();
            while (rs.next()){
                String clase = rs.getString(1);
                String codigoVuelo = rs.getString(2);
                if (clase.equals(Vuelo.EJECUTIVA)){
                    stmUpdate = conn.prepareStatement("UPDATE vuelo SET cantidadAsientosEjecutiva = cantidadAsientosEjecutiva+1" +
                                                      " WHERE codigo = ?");
                }
                else if (clase.equals(Vuelo.PRIMERA)){
                    stmUpdate = conn.prepareStatement("UPDATE vuelo SET cantidadAsientosPrimera = cantidadAsientosPrimera+1" +
                                                      " WHERE codigo = ?");
                }
                else {
                    stmUpdate = conn.prepareStatement("UPDATE vuelo SET cantidadAsientosTurista = cantidadAsientosTurista+1" +
                                                      " WHERE codigo = ?");
                }
                
                stmUpdate.setString(1, codigoVuelo);
                stmUpdate.execute();
            }
            
            
            stmDelete = conn.prepareStatement("DELETE from RESERVA WHERE DATEDIFF(CURDATE(), fecha)  > 30");            
                        
            stmDelete.execute();            
        } catch (SQLException ex) {
            throw new RuntimeException("Error al eliminar reservas viejas",ex);
        } finally {
            factory.closeStatement(stm);
            factory.closeStatement(stmDelete);
            factory.closeStatement(stmUpdate);
            factory.closeConnection(conn);
        }
    }

    public void update(Reserva reserva) {
        Connection conn = null;
        ConnectionFactory factory = ConnectionFactory.getInstance();
        PreparedStatement stm = null;
        try {
            conn = factory.getConnection();            
            stm = conn.prepareStatement("UPDATE reserva SET fechaCompra = ? WHERE codigo=?");
            stm.setDate(1, new Date(reserva.getFechaCompra().getTime()));
            stm.setString(2, reserva.getCodigo());
                        
            stm.execute();            
        } catch (SQLException ex) {
            throw new RuntimeException("Error hacer el update de la reserva",ex);
        } finally {
            factory.closeStatement(stm);
            factory.closeConnection(conn);
        }
    }
}
